/*-----------------------------------------------------------------------------!
! Inventory Database script                                                    !
!------------------------------------------------------------------------------!
! System   : Inventory Management System                                       !
! Client   : UK based                                                          !
! Date     : April 2011                                                        !
! Copyright: (C) KAWISOFT Inc                                                  !
! Program  : InventorySystem_ddy                                               !
! Version  : v1.00                                                             !
! Function : Create database and table                                         !
!------------------------------------------------------------------------------!
! Notes:                                                                       !
! Original author: Shaiful Islam & Amit Kumar Mondol, April 24 2011        !
! Last changed by: Shaiful Islam, April 26 2011                            !
===============================================================================*/



--Create InventorySystem database
IF EXISTS (SELECT 1 FROM master.dbo.sysdatabases WHERE name = 'InventorySystem')
BEGIN
	DROP DATABASE InventorySystem;
END
GO

CREATE DATABASE InventorySystem;
GO

use InventorySystem
GO

-- Create Product table
create table Product
(
   Id varchar(20), 
   Code varchar(50),
   Name varchar(50),
   Description varchar(200),
   Price float,
   Vat varchar(1),
   DelCharge float,
   Quantity int,
   SizeId varchar(20),
   ColourId varchar(20), 
   Live varchar(1),
   CONSTRAINT Product_pKey PRIMARY KEY(Id)
);

-- Create ProductColor table
create table ProductColor
(
   Id varchar(20),
   Name varchar(50),
   Live varchar(1)
   CONSTRAINT ProductColor_pKey PRIMARY KEY(Id)	
);


-- Create ProductSize table
create table ProductSize
(
   Id varchar(20),
   Name varchar(50),
   Live varchar(1)
   CONSTRAINT ProductSize_pKey PRIMARY KEY(Id)	
);

-- Create SaleType table
create table SaleType
(
   Id varchar(20),
   Name varchar(50),
   Live varchar(1)
   CONSTRAINT SaleType_pKey PRIMARY KEY(Id)	
);

-- Create Customer table
create table Customer
(
   Id varchar(20), 
   Reference varchar(50),
   CompanyName varchar(200),
   ContactPerson varchar(20),
   Live varchar(1),
   SaleTypeId varchar(20),
   CONSTRAINT Customer_pKey PRIMARY KEY(Id)	
);

-- Create ProductColor table
create table Supplier
(
   Id varchar(20), 
   AccountReference varchar(50),
   CompanyName varchar(200),
   ContactPerson varchar(20),
   Live varchar(1),
   CONSTRAINT Supplier_pKey PRIMARY KEY(Id)	
);

-- Create ProductColor table
create table Address
(
   Id varchar(20), 
   Description varchar(200),
   PostCode varchar(50),
   Country varchar(50),
   Telephone varchar(20),
   Mobile varchar(20), 
   Fax varchar(20),
   Email varchar(20), 
   Web varchar(20),
   PersonId varchar(20),
   Live varchar(1),
   CONSTRAINT Address_pKey PRIMARY KEY(Id)	
);

-- Create TablesId table
create table TablesId
(
   SequenceName varChar(50) not null,
   Increment int not null,
   CurrentValue varChar(20) not null
);